﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using UnityEngine;
using System.IO;
using System.Linq;
using BearUtil;
using ExcelDataReader;
using OfficeOpenXml;
using FileMode = System.IO.FileMode;
using UnityEditor;

/// <summary>
/// Excel 编辑类
/// </summary>
public static class ExcelEdit
{
    /// <summary>
    /// 读取 Excel 文件
    /// </summary>
    /// <param name="filePath"></param>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public static void LoadExcel(string filePath, ref List<ExcelData> datas)
    {
        using (var stream = File.Open(filePath, FileMode.Open, FileAccess.ReadWrite))
        {
            if (!stream.CanRead)
            {
                Debug.Log("Excel 文件读取失败");
                return;
            }

            using (var reader = ExcelReaderFactory.CreateOpenXmlReader(stream))
            {
                var result = reader.AsDataSet();
                if (result == null)
                    return;
                if (datas != null && datas.Count() > 0)
                    datas.Clear();
                else
                    datas = new List<ExcelData>();

                foreach (DataTable resultTable in result.Tables)
                {
                    ExcelData data = new ExcelData();
                    data.Init(resultTable);
                    datas.Add(data);
                }
            }
        }
    }

    /// <summary>
    /// 写入 Excel 文件
    /// </summary>
    /// <param name="datas"></param>
    /// <param name="path"></param>
    public static void WriteExcel(ExcelData data, string path)
    {
        // 输出 excel
        Utils.CheckFold(path);

        try
        {
            if (data == null || data.Datas.Length <= 0)
                return;

            string filePath = path + "/" + data.name + ".xlsx";

            if (data.name.Equals("") || data.sheetName.Equals(""))
            {
                Debug.LogWarning("丢失数据命名");
                return;
            }

            if (File.Exists(filePath))
                File.Delete(filePath);
            
            var excelFile = new FileStream(@filePath, FileMode.Create);
            using (var excel = new ExcelPackage(excelFile))
            {
                ExcelWorksheet ws = excel.Workbook.Worksheets.Add(data.sheetName);
                
                for (int x = 0; x < data.col; x++)
                {
                    for (int y = 0; y < data.row; y++)
                    {
                        ws.Cells[y + 1, x + 1].Value = data.Datas[x, y];
                    }
                    ws.Column(x + 1).Width = 20;
                }
                excel.Save();
            }

            Debug.Log("保存成功: " + data.name) ;
            excelFile.Close();
            AssetDatabase.Refresh();
        }
        catch (Exception e)
        {
            Debug.LogError(e);
        }
    }

}